package com.sxhalo.fsms.controller.api;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.sxhalo.fsms.controller.BaseController;
import com.sxhalo.fsms.controller.UploadController;
import com.sxhalo.fsms.domain.*;
import com.sxhalo.fsms.export.ExcelUtils;
import com.sxhalo.fsms.export.ExportSetInfo;
import com.sxhalo.fsms.export.ImportExcelUtils;
import com.sxhalo.fsms.log.SysControllerLog;
import com.sxhalo.fsms.quartz.DeviceAttrEnum;
import com.sxhalo.fsms.quartz.DeviceTypeEnum;
import com.sxhalo.fsms.service.*;
import com.sxhalo.fsms.util.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Controller
@RequestMapping("/api")
public class DeviceController extends BaseController {

	@Autowired
	private IFactDeviceService deviceService;
	@Autowired
	private ISysDeviceTypeService deviceTypeService;
	@Autowired
	private IFactAreaService areaService;
	@Autowired
	private IMotorRunningDataService runningDataServicek;

	@Autowired
	private ITransducerRunningDataService transducerRunningDataServicek;


    @Autowired
    private IDeviceEmRunningDataService emRunningDataService;


	@Autowired
	private UploadController uploadController = new UploadController();

	@ResponseBody
	@RequestMapping(value = "/device", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult findDevice(Integer current, Integer areaId, Integer pageSize, HttpServletRequest req) {
		String where =getWhere(req);
		String order = " a.device_id desc";

		Integer start = pageSize * (current - 1);
		Integer count = deviceService.getChildCount(where, areaId);
		List<FactDevice> list = deviceService.findChildPagerList(start, pageSize, where, order, areaId);
		PageData page = new PageData(current, pageSize, count, list);
		return ApiResult.retOK(req, page);
	}

	@ResponseBody
	@RequestMapping(value = "/device/list", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult findDeviceList(HttpServletRequest req) {
		String where =getWhere(req);
		String order = "update_time desc";
		if (!isBlank(req.getParameter("searchValue"))) {
			String value = filterString(req.getParameter("searchValue").trim());
			where += " and (device_no like '%" + value + "%' or device_name like '%" + value + "%' or device_brand like '%" + value + "%' or device_model like '%" + value + "%')";
		}
		if (!isBlank(req.getParameter("areaId"))) {
			where += " and area_id=" + req.getParameter("areaId").trim();
		}
		if (!isBlank(req.getParameter("deviceTypeId"))) {
			where += " and device_type_id=" + req.getParameter("deviceTypeId").trim();
		}


		return findList(null, null, where, order, deviceService);
	}



	@ResponseBody
	@RequestMapping(value = "/device/simpelist", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult findDeviceSimpeList(HttpServletRequest req) {
		String where =getWhere(req);
		String order = "update_time desc";
		if (!isBlank(req.getParameter("areaId"))) {
			where += " and area_id=" + req.getParameter("areaId").trim();
		}
		if (!isBlank(req.getParameter("deviceTypeId"))) {
			where += " and device_type_id=" + req.getParameter("deviceTypeId").trim();
		}
		List<FactDevice> list = deviceService.findWhereList(where, order);
		return ApiResult.retOK(req, list);
	}
	
	@ResponseBody
	@RequestMapping(value = "/device/realtime", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult findDeviceRealtime(Integer current, Integer pageSize, HttpServletRequest req) {
		current=current==null?1:current;
		String where = "delete_flag=0 and use_state=1 ";
		String order = " update_time desc";

		if (!isBlank(req.getParameter("searchValue"))) {
			where += " and ( device_no like '%" + req.getParameter("searchValue").trim() + "%' or  device_name like '%" + req.getParameter("searchValue").trim() + "%' or  device_brand like '%" + req.getParameter("searchValue").trim() + "%' or  device_model like '%" + req.getParameter("searchValue").trim() + "%')" ;
		}
		if (!isBlank(req.getParameter("deviceTypeId"))) {
			where += " and device_type_id like '%" + req.getParameter("deviceTypeId").trim() + "%'";
		}

		if (!isBlank(req.getParameter("type"))) {
			 if(req.getParameter("type").equals("800")){
				 where += " and alarm_level is null";
			 }else{
				 where += " and alarm_level = '" + req.getParameter("type").trim() + "'";
			 }

		}



		String sortType=req.getParameter("sort");
		if(sortType.equals("2")){
			order = " online desc,alarm  , trip  ";
        }else if(sortType.equals("3")){
            order = " area_id ";
        }
		Integer areaId = null;
		if (!isBlank(req.getParameter("areaId"))) {
			areaId = Integer.parseInt(req.getParameter("areaId"));
		}

		Integer start = pageSize * (current - 1);

		Integer count = deviceService.getRealDataCount(where, areaId);
		List<FactDevice> list = deviceService.findRealDataPagerList(start, pageSize, where, order, areaId);
        List<Integer> deviceIds=new ArrayList<Integer>();
		PageData page = new PageData(current, pageSize, count, list);

		return ApiResult.retOK(req, page);

	}


	@ResponseBody
	@RequestMapping(value = "/device/realtime/count", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult findDeviceRealtimeCount(HttpServletRequest req) {

		String where = "delete_flag=0 and use_state=1 and   online=1 ";



		Integer areaId = null;
		if (!isBlank(req.getParameter("areaId"))) {
			areaId = Integer.parseInt(req.getParameter("areaId"));
		}


		if (!isBlank(req.getParameter("searchValue"))) {
			where += " and ( device_no like '%" + req.getParameter("searchValue").trim() + "%' or  device_name like '%" + req.getParameter("searchValue").trim() + "%' or  device_brand like '%" + req.getParameter("searchValue").trim() + "%' or  device_model like '%" + req.getParameter("searchValue").trim() + "%')" ;
		}
		if (!isBlank(req.getParameter("deviceTypeId"))) {
			where += " and device_type_id like '%" + req.getParameter("deviceTypeId").trim() + "%'";
		}

		if (!isBlank(req.getParameter("type"))) {
			if(req.getParameter("type").equals("800")){
				where += " and alarm_level is null";
			}else{
				where += " and alarm_level = '" + req.getParameter("type").trim() + "'";
			}

		}
		Map<String, Object> map = new HashMap<>();
//		Integer allCount = deviceService.getCount("delete_flag=0 and use_state=1");
		Integer onlineNum = deviceService.getRealDataCount(where, areaId);
		map.put("onlineNum",onlineNum);


		return ApiResult.retOK(req, map);

	}

	@ResponseBody
	@SysControllerLog(operationName = "新增设备", modelName = "设备管理")
	@RequestMapping(value = "/device", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")
	public ApiResult saveDevice(@RequestBody FactDevice record, HttpServletRequest req) {
		Timestamp nowtime = new Timestamp(System.currentTimeMillis());
		record.setUpdateTime(nowtime);
		record.setDeleteFlag(0);
		record.setCreateTime(nowtime);
		deviceService.insertSelective(record);

		if(record.getUseState()==1){
			CacheUtil.removeObject("devices");
		}

		return ApiResult.retOK(req);
	}

	@ResponseBody
	@SysControllerLog(operationName = "批量新增设备", modelName = "设备管理")
	@RequestMapping(value = "/device/batchSave", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")
	public ApiResult saveDevice(FactDevice record, Integer createNum, String devicename, HttpServletRequest req) {

		deviceService.batchInsert(record, createNum, devicename);


		return ApiResult.retOK(req);
	}

	@ResponseBody
	@SysControllerLog(operationName = "批量更新设备", modelName = "设备管理")
	@RequestMapping(value = "/device/batchUpdate", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")
	public ApiResult saveDevice(FactDevice record, String ids, HttpServletRequest req) {
		record.setUpdateTime(new Timestamp(System.currentTimeMillis()));
		deviceService.batchUpdate(record, " device_id in (" + ids + ")");

		CacheUtil.removeObject("devices");
		return ApiResult.retOK(req);
	}


	@ResponseBody
	@SysControllerLog(operationName = "批量更新启用状态", modelName = "设备管理")
	@RequestMapping(value = "/device/batchUpdateUseState", method = RequestMethod.POST, produces = "application/json;charset=UTF-8")
	public ApiResult saveDevice( String ids,Integer useState,Integer simulation, HttpServletRequest req) {
		FactDevice record=new FactDevice();
		record.setUpdateTime(new Timestamp(System.currentTimeMillis()));
		if(useState!=null){
			record.setUseState(useState);
		}else{
			record.setSimulation(simulation);
		}

		deviceService.batchUpdate(record, " device_id in (" + ids + ")");

		CacheUtil.removeObject("devices");
		return ApiResult.retOK(req);
	}


	@ResponseBody
	@SysControllerLog(operationName = "编辑设备", modelName = "设备管理")
	@RequestMapping(value = "/device", method = RequestMethod.PUT, produces = "application/json;charset=UTF-8")
	public ApiResult updateDevice(@RequestBody FactDevice record, String method, HttpServletRequest req) {

		record.setUpdateTime(new Timestamp(System.currentTimeMillis()));
		deviceService.updateByPrimaryKeySelective(record);

		CacheUtil.removeObject("devices");
		return ApiResult.retOK(req);
	}

	@ResponseBody
	@SysControllerLog(operationName = "删除", modelName = "设备管理")
	@RequestMapping(value = "/device", method = RequestMethod.DELETE, produces = "application/json;charset=UTF-8")
	public ApiResult delDevice(Integer deviceId, String method, HttpServletRequest req) {
		FactDevice record = new FactDevice();
		record.setDeviceId(deviceId);
		Timestamp nowtime = new Timestamp(System.currentTimeMillis());
		record.setDeleteFlag(1);
		record.setUpdateTime(nowtime);
		deviceService.updateByPrimaryKeySelective(record);

		CacheUtil.removeObject("devices");
		return ApiResult.retOK(req);
	}


	@ResponseBody
	@SysControllerLog(operationName = "批量删除", modelName = "设备管理")
	@RequestMapping(value = "/device/batchdel", method = RequestMethod.DELETE, produces = "application/json;charset=UTF-8")
	public ApiResult batchDelDevice(String  ids, String method, HttpServletRequest req) {
		deviceService.deleteByWhere(" device_id in ("+ids+")");

		CacheUtil.removeObject("devices");
		return ApiResult.retOK(req);
	}

	@ResponseBody
	@SysControllerLog(operationName = "实时数据详情页", modelName = "设备管理")
	@RequestMapping(value = "/device/monitoring/{id}", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult getMonitoringDevicInfo(@PathVariable("id") Integer id, HttpServletRequest req) {
		Map<String, Object> map = new HashMap<>();
		FactDevice device = deviceService.selectByPrimaryKey(id);
//		if (device.getAreaId() != null) {
//			String areaName = areaService.getAllParentNameByParentId(null, device.getAreaId());
//			device.setAreaName(areaName);
//		}

		 RedisData data=deviceService.getLiveData(id);
		//RedisData data = new RedisData();
		if(data!=null)data.setAcquisitionTime(new Date());
		map.put("deviceInfo", device);
		map.put("runningData", data);
		return ApiResult.retOK(req, map);
	}





	@ResponseBody
	@SysControllerLog(operationName = "查看设备详情", modelName = "设备管理")
	@RequestMapping(value = "/device/{id}", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult getDevicInfo(@PathVariable("id") Integer id, HttpServletRequest req) {
		FactDevice device = deviceService.selectByPrimaryKey(id);
//		String areaName = areaService.getAllParentNameByParentId(null, device.getAreaId());
//		device.setAreaName(areaName);
		return ApiResult.retOK(req, device);
	}

	/*
	 * 实时数据历史曲线数据
	 */
	@ResponseBody
	@RequestMapping(value = "/device/monitoring/{id}/phase", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult getDevicPhaseHistory(@PathVariable("id") Integer id, Integer type, HttpServletRequest req) {
	    if(DeviceTypeEnum.TRANSDUCER.getIndex()==type){
			return ApiResult.retOK(req, getTransducerPhaseHistoryData(id));

		}else if(DeviceTypeEnum.WATT_HOUR.getIndex()==type){
			return ApiResult.retOK(req, getSmartMeterHistoryData(id));

		}else{
			return ApiResult.retOK(req, getMotorPhaseHistoryData(id));
		}

//	    else if(DeviceTypeEnum.THREE_WATT_HOUR.getIndex()==type){
//            return ApiResult.retOK(req, getThreePhaseHistoryData(id));
//
//        }

	}



	/*
	 * 实时数据历史曲线数据
	 */
	@ResponseBody
	@RequestMapping(value = "/device/monitoring/design/{id}/phase", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult getDesignDevicPhaseHistory(Integer deviceType,Integer id,  HttpServletRequest req) {
		SysDeviceType type=deviceTypeService.selectByPrimaryKey(deviceType);

		String where="  device_id=80000001 ";
		//String where=" device_id= " + id +" and acquisition_time>DATE_SUB(NOW(),INTERVAL 60 MINUTE)";
		String template=type.getTemplate();
		if(template==null||template.equals("")){
			return null;
		}


		HashMap<String,Object> templateObject= JSONObject.parseObject(template,HashMap.class);
		List<Map<String, Object>> dataList = null;
		List<JSONObject> tabs=(List<JSONObject>)templateObject.get("tabitem");
		ArrayList<Map<String, Object>> returnata = new ArrayList<>();

		for (JSONObject tab:tabs ) {
			if(tab.get("label").equals("报警记录")){
				continue;
			}
            String  filedStr="";
			JSONArray fileds=(JSONArray)tab.get("filed");
			for (Object filed:fileds ) {
				filedStr+=","+StringUtils.camel4underline(filed.toString());
			}
			filedStr=filedStr.substring(1);
			List<HashMap>  phaseList =deviceService.findAttrHis(filedStr,where,type.getTableName());
			Object[] phaseTime = new Object[phaseList.size()];
            ArrayList<Object[]>  phases=new ArrayList<>();
			Map<String, Object> itemData=null;
			for (int i = 0; i < phaseList.size(); i++) {
				for (int j=0;j< fileds.size();j++ ) {
					if(i==0){
						Object[]  phase=new Object[phaseList.size()];
						phases.add(phase);
					}
					String filed=StringUtils.camel4underline(fileds.get(j).toString());
					phases.get(j)[i]=phaseList.get(i).get(filed);
					phaseTime[i]=  phaseList.get(i).get("acquisition_time");
				}
			}
			JSONArray browseNames=(JSONArray)tab.get("browseNames");
			dataList = new ArrayList<Map<String, Object>>();
			for (int j=0;j< browseNames.size();j++ ) {
				itemData=new HashMap<String, Object>();
				itemData.put("name",browseNames.get(j));
				itemData.put("data",phases.get(j));
				itemData.put("type",tab.get("chartType"));
				dataList.add(itemData);
			}

			JSONObject chartData=(JSONObject)tab.get("chartData");
			chartData.put("series",dataList);

			JSONObject legendData=(JSONObject)chartData.get("legend");
			legendData.put("data",browseNames);
			chartData.put("legend",legendData);
			JSONObject xAxisData=(JSONObject)chartData.get("xAxis");
			xAxisData.put("data",phaseTime);
			chartData.put("xAxis",xAxisData);

//			itemChartData=new HashMap<>();
//			itemChartData.put("data",dataList);
//			itemChartData.put("phaseTime",phaseTime);
			returnata.add(chartData);

		}

		return ApiResult.retOK(req, returnata);
	}




	private Map<String, Object>  getMotorPhaseHistoryData(Integer id){
		Map<String, Object> returnata = new HashMap<String, Object>();
		List<MotorRunningData> phaseList = runningDataServicek.findPhaseHis("device_id= " + id +" and acquisition_time>DATE_SUB(NOW(),INTERVAL 60 MINUTE)");
		Object[] phaseTime = new Object[phaseList.size()];
		Object[] phaseA = new Object[phaseList.size()];
		Object[] phaseB = new Object[phaseList.size()];
		Object[] phaseC = new Object[phaseList.size()];
		Object[] voltageA = new Object[phaseList.size()];
		Object[] voltageB = new Object[phaseList.size()];
		Object[] voltageC = new Object[phaseList.size()];
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> voltageDataList = new ArrayList<Map<String, Object>>();
		Map<String, Object> itemData=new HashMap<String, Object>();

		for (int i = 0; i < phaseList.size(); i++) {
			phaseTime[i] = phaseList.get(i).getAcquisitionTime();
			phaseA[i] = phaseList.get(i).getPhaseaCurrent();
			phaseB[i] = phaseList.get(i).getPhasebCurrent();
			phaseC[i] = phaseList.get(i).getPhasecCurrent();
			voltageA[i] = phaseList.get(i).getUabLineVoltage();
			voltageB[i] = phaseList.get(i).getUbcLineVoltage();
			voltageC[i] = phaseList.get(i).getUcaLineVoltage();
		}
		itemData.put("name","A相电流");
		itemData.put("data",phaseA);
		itemData.put("type","line");
		dataList.add(itemData);
		itemData=new HashMap<String, Object>();
		itemData.put("name","B相电流");
		itemData.put("data",phaseB);
		itemData.put("type","line");
		dataList.add(itemData);
		itemData=new HashMap<String, Object>();
		itemData.put("name","C相电流");
		itemData.put("data",phaseC);
		itemData.put("type","line");
		dataList.add(itemData);
		returnata.put("phaseTime", phaseTime);
		returnata.put("data", dataList);

		itemData=new HashMap<String, Object>();
		itemData.put("name","线电压Uab");
		itemData.put("data",voltageA);
		itemData.put("type","line");
		voltageDataList.add(itemData);
		itemData=new HashMap<String, Object>();
		itemData.put("name","线电压Ubc");
		itemData.put("data",voltageB);
		itemData.put("type","line");
		voltageDataList.add(itemData);
		itemData=new HashMap<String, Object>();
		itemData.put("name","线电压Uca");
		itemData.put("data",voltageC);
		itemData.put("type","line");
		voltageDataList.add(itemData);
		returnata.put("phaseTime", phaseTime);
		returnata.put("data1", voltageDataList);
		return returnata;

	}

   //电能表
	private Map<String, Object>  getSmartMeterHistoryData(Integer id){
		Map<String, Object> returnata = new HashMap<String, Object>();
		List<HashMap> phaseList = emRunningDataService.findAttrHis("phaseA_current,phaseB_current,phaseC_current,total_active_energy","device_id= " + id +" and acquisition_time>DATE_SUB(NOW(),INTERVAL 60 MINUTE)");
		Object[] phaseTime = new Object[phaseList.size()];

		Object[] activePowers = new Object[phaseList.size()];
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> dataList1 = new ArrayList<Map<String, Object>>();
		Map<String, Object> itemData=new HashMap<String, Object>();
		Object[] phaseA = new Object[phaseList.size()];
		Object[] phaseB = new Object[phaseList.size()];
		Object[] phaseC = new Object[phaseList.size()];
		for (int i = 0; i < phaseList.size(); i++) {
			phaseTime[i] = phaseList.get(i).get("acquisition_time");
			phaseA[i] = phaseList.get(i).get("phaseA_current");
			phaseB[i] = phaseList.get(i).get("phaseB_current");;
			phaseC[i] = phaseList.get(i).get("phaseC_current");;
			activePowers[i] = phaseList.get(i).get("total_active_energy");

		}
		itemData.put("name","用电量");
		itemData.put("data",activePowers);
        itemData.put("barWidth","60%");
        itemData.put("type","bar");
		dataList1.add(itemData);

		itemData=new HashMap<String, Object>();
		itemData.put("name","A相电流");
		itemData.put("data",phaseA);
		itemData.put("type","line");
		dataList.add(itemData);
		itemData=new HashMap<String, Object>();
		itemData.put("name","B相电流");
		itemData.put("data",phaseB);
		itemData.put("type","line");
		dataList.add(itemData);
		itemData=new HashMap<String, Object>();
		itemData.put("name","C相电流");
		itemData.put("data",phaseC);
		itemData.put("type","line");
		dataList.add(itemData);
		returnata.put("phaseTime", phaseTime);
		returnata.put("data", dataList);

		returnata.put("phaseTime", phaseTime);

		returnata.put("data1", dataList1);

		return returnata;

	}




	//变频器
	private Map<String, Object>  getTransducerPhaseHistoryData(Integer id){
		Map<String, Object> returnata = new HashMap<String, Object>();
		List<TransducerRunningData> phaseList = transducerRunningDataServicek.findPhaseHis("device_id= " + id +" and acquisition_time>DATE_SUB(NOW(),INTERVAL 60 MINUTE)");
		Object[] phaseTime = new Object[phaseList.size()];
		BigDecimal[] phaseA = new BigDecimal[phaseList.size()];
		BigDecimal[] phaseB = new BigDecimal[phaseList.size()];
		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> dataList1 = new ArrayList<Map<String, Object>>();
		Map<String, Object> itemData=new HashMap<String, Object>();
		for (int i = 0; i < phaseList.size(); i++) {
			phaseTime[i] = phaseList.get(i).getAcquisitionTime();
			phaseA[i] = phaseList.get(i).getOperatingFrequency();
			phaseB[i] = phaseList.get(i).getOutputCurrent();
		}
		itemData.put("name","运行频率（hz）");
		itemData.put("data",phaseA);
		itemData.put("type","line");
		dataList.add(itemData);
		itemData=new HashMap<String, Object>();
		itemData.put("name","输出电流（A）");
		itemData.put("data",phaseB);
		itemData.put("type","line");
		dataList1.add(itemData);

		returnata.put("phaseTime", phaseTime);
		returnata.put("data", dataList);
		returnata.put("data1", dataList1);

		return returnata;

	}






	// 表头选择
	private List<String[]> createUsersExcelHead() {
		List<String[]> headlist = new ArrayList<String[]>();
		String[] row1 = {  "所在区域", "设备类型", "设备编号", "设备名称", "设备品牌",  "设备型号","通讯协议", "最新维保时间", "是否启用","\t\n" +
				"供货商", "供货商联系人姓名", "供货商联系人电话" };
		headlist.add(row1);
		return headlist;
	}

	@ResponseBody
	@SysControllerLog(operationName = "导出", modelName = "设备管理")
	@RequestMapping(value = "/exportDeviceExcel", method = RequestMethod.GET)
	public void deviceDownload(Integer areaId, HttpServletRequest request, HttpServletResponse response)
			throws IOException {
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		String fileName = "设备列表";
		String[] Userkeys = {  "areaName", "deviceTypeName", "deviceNo","deviceName",
				"deviceBrand", "deviceModel", "protocolType", "latestMaintenanceTime", "useState","supplier","supplierName","supplierPhone"};

		String where =getWhere(request);
		String order = " a.device_id desc";


		List<FactDevice> data = deviceService.findChildPagerList(0, -1, where, order, areaId);
    	ExportSetInfo info = new ExportSetInfo();
		List<Map<String, Object>> coalSalesList = createUserListRecord(data);
		info.setHeadNames(createUsersExcelHead());
		info.setDatalist(coalSalesList);
		info.setTitles(fileName);
		info.setFieldNames(Userkeys);
		info.setOut(os);
		ExcelUtils.export2Excel(info);
		ExcelUtils.ExcelDownload(fileName, response, os);

	}

	private List<Map<String, Object>> createUserListRecord(List<FactDevice> data) {
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>();
		for (FactDevice fd : data) {
			Map<String, Object> mapValue = new HashMap<String, Object>();

			mapValue.put("areaName", fd.getAreaName());
			mapValue.put("deviceTypeName", fd.getDeviceTypeName());
			mapValue.put("deviceNo", fd.getDeviceNo());
			mapValue.put("deviceName", fd.getDeviceName());
			mapValue.put("deviceBrand", fd.getDeviceBrand());
			mapValue.put("deviceModel", fd.getDeviceModel());
			mapValue.put("protocolType", fd.getProtocolTypeName());
			mapValue.put("latestMaintenanceTime", fd.getLatestMaintenanceTime()==null?"":dateFormat.format(fd.getLatestMaintenanceTime()));
			mapValue.put("useState", fd.getUseState()==1?"启用":"禁用");
			mapValue.put("supplier", fd.getSupplier());
			mapValue.put("supplierName", fd.getSupplierName());
			mapValue.put("supplierPhone", fd.getSupplierPhone());

			listmap.add(mapValue);
		}

		return listmap;
	}

	@ResponseBody
	@RequestMapping(value = "/device/batchImport", method = RequestMethod.POST)
	public ApiResult fileUpload(MultipartFile file, HttpServletRequest request) {
		SysAttachments as = uploadController.saveDocument(file, "device", request);
		List<FactDevice> devices = new ArrayList<FactDevice>();
		int successNum = 0;
		DateFormat formater	 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		try {
			List areas=areaService.findWhereList("delete_flag=0","");
			List<Object[]> objs = ImportExcelUtils.importExcelBySheetIndex(as.getPath(), 0, 0, 2);
			FactDevice device = null;
			for (Object[] obj : objs) {
				device = new FactDevice();
				device.setAreaId(getArea(areas,DataFormatUtils.objectToString(obj[0])));
				device.setDeviceTypeId(DataFormatUtils.objectToInteger(obj[1]));
//				device.setDriverId(DataFormatUtils.objectToInteger(obj[2]));
//				device.setProtocolType(DataFormatUtils.objectToString(obj[3]));
//				device.setProtocolConfig(DataFormatUtils.objectToString(obj[4]));
				device.setDeviceNo(DataFormatUtils.objectToString(obj[2]));
				device.setDeviceName(DataFormatUtils.objectToString(obj[3]));
				device.setDevicePosition(DataFormatUtils.objectToString(obj[4]));
				device.setDeviceDesc(DataFormatUtils.objectToString(obj[5]));
				device.setDeviceBrand(DataFormatUtils.objectToString(obj[6]));
				device.setDeviceModel(DataFormatUtils.objectToString(obj[7]));
				device.setSeriaNumber(DataFormatUtils.objectToString(obj[8]));

				device.setOnlineTime(DataFormatUtils.objectToDate(obj[9], null));
				device.setLatestMaintenanceTime(DataFormatUtils.objectToDate(obj[10], null));

				device.setSupplier(DataFormatUtils.objectToString(obj[11]));
				device.setSupplierName(DataFormatUtils.objectToString(obj[12]));
				device.setSupplierPhone(DataFormatUtils.objectToString(obj[13]));
				device.setUseState(0);
				devices.add(device);

			}
			successNum = deviceService.batchInsert(devices);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ApiResult.retOK(request, successNum);
	}

	private Integer getArea(List<FactArea> areas,String areaStr){
		for(FactArea area:areas){
			if(areaStr.equals(area.getAreaName())){
				return area.getAreaId();
			}
		}
		FactArea area=new FactArea();
		area.setAreaName(areaStr);
		area.setCreateTime(new Date());
		area.setUpdateTime(new Date());
		area.setParentId(0);
		area.setUseState(0);
		area.setDeleteFlag(0);
		areaService.insertSelective(area);
		areas.add(area);
		return area.getAreaId();

	}

	// 首页统计
	@ResponseBody
	@RequestMapping(value = "/device/statistics", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult getDevicCount(Integer areaId, HttpServletRequest req) {
		Map<String, Object> statistics = deviceService.getStatistics(areaId == null ? ""
				: "   FIND_IN_SET(area_id,fun_get_area_chindids(" + areaId
						+ ")) and  FIND_IN_SET(area_id,fun_get_area_chindids(" + areaId + ")) ");

		return ApiResult.retOK(req, statistics);
	}


   private String getWhere( HttpServletRequest req) {
		String where = "a.delete_flag=0";

		if (!isBlank(req.getParameter("deviceTypeId"))) {
			where += " and device_type_id = " + req.getParameter("deviceTypeId").trim();
		}
		if (!isBlank(req.getParameter("protocolType"))) {
			where += " and protocol_type = " + req.getParameter("protocolType").trim();
		}
		if (!isBlank(req.getParameter("latestMaintenanceTime"))) {
			where += " and latest_maintenance_time like '%" + req.getParameter("latestMaintenanceTime").trim() + "%'";
		}
		if (!isBlank(req.getParameter("deviceNo"))) {
			where += " and device_no like '%" + req.getParameter("deviceNo").trim() + "%'";
		}
		if (!isBlank(req.getParameter("deviceName"))) {
			where += " and device_name like '%" + req.getParameter("deviceName").trim() + "%'";
		}

		if (!isBlank(req.getParameter("deviceBrand"))) {
			where += " and device_brand like '%" + req.getParameter("deviceBrand").trim() + "%'";
		}
		if (!isBlank(req.getParameter("deviceModel"))) {
			where += " and device_model like '%" + req.getParameter("deviceModel").trim() + "%'";
		}
	   if (!isBlank(req.getParameter("useState"))) {
		   where += " and a.use_state = " + req.getParameter("useState") ;
	   }

		return where;
	}


	/*
	 * 实时数据历史曲线数据
	 */
	@ResponseBody
	@RequestMapping(value = "/device/history/data", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult getDevicHistoryData(Integer deviceId, Integer  attrId, HttpServletRequest req) {
		DeviceAttrEnum attrEnum=DeviceAttrEnum.getEnumByKey(attrId);
		FactDevice device=deviceService.selectByPrimaryKey(deviceId);
		String attr=StringUtils.camel4underline(attrEnum.getMsg());
		List<HashMap> data=new ArrayList<HashMap>();
		if(DeviceTypeEnum.TRANSDUCER.getIndex()==device.getDeviceTypeId()){
			try{
				data=transducerRunningDataServicek.findAttrHis(attr +" as value","device_id= " + deviceId +" and acquisition_time>DATE_SUB(NOW(),INTERVAL 60 MINUTE)");
			}catch(Exception e){
				System.out.println(e);
			}
		}else if(DeviceTypeEnum.WATT_HOUR.getIndex()==device.getDeviceTypeId()){
			try{
				data=emRunningDataService.findAttrHis(attr+" as value","device_id= " + deviceId +" and acquisition_time>DATE_SUB(NOW(),INTERVAL 60 MINUTE)");
			}catch(Exception e){
				System.out.println(e);
			}
		  } else{
			try{
				data=runningDataServicek.findAttrHis(attr +" as value","device_id= " + deviceId +" and acquisition_time>DATE_SUB(NOW(),INTERVAL 60 MINUTE)");
			}catch(Exception e){
				System.out.println(e);
			}
		}
		Object[] phaseTime = new Object[data.size()];
		Object[] value = new Object[data.size()];

		List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
		List<Map<String, Object>> dataList1 = new ArrayList<Map<String, Object>>();
		Map<String, Object> itemData=new HashMap<String, Object>();
		for (int i = 0; i < data.size(); i++) {
			phaseTime[i] = data.get(i).get("acquisition_time");
			value[i] = data.get(i).get("value");
		}
		itemData.put("phaseTime", phaseTime);

		itemData.put("data",value);
		return ApiResult.retOK(req, itemData);

	}



	@ResponseBody
	@RequestMapping(value = "/device/history/list", method = RequestMethod.GET, produces = "application/json;charset=UTF-8")
	public ApiResult findDeviceHistoryList(Integer current,  Integer pageSize, Integer id,Integer type,HttpServletRequest req) {
		String where =" device_id ="+id;
		String order = "acquisition_time desc";
		if (!isBlank(req.getParameter("time"))) {
			Map<String, String[]> params=req.getParameterMap();
			String[] times=params.get("time");
			where+=" and acquisition_time >= '"+times[0]+" 00:00:00' and  acquisition_time<= '"+times[1]+" 23:59:59'";
		}

		if(type==1){
			return findList(current, pageSize, where, order, transducerRunningDataServicek);
		}else if(type==2){
			return findList(current, pageSize, where, order, runningDataServicek);
		} else{
			return findList(current, pageSize, where, order, emRunningDataService);
		}

	}



}
